package com.gemini.main.tablestore;

import com.alicloud.openservices.tablestore.SyncClient;
import com.alicloud.openservices.tablestore.model.search.SearchQuery;
import com.alicloud.openservices.tablestore.model.search.SearchRequest;
import com.alicloud.openservices.tablestore.model.search.SearchResponse;
import com.alicloud.openservices.tablestore.model.search.agg.AggregationBuilders;
import com.alicloud.openservices.tablestore.model.search.query.QueryBuilders;

/**
 * gemini
 * com.gemini.main.tablestore.StatisticsSample
 *
 * @author zhanghailin
 */
public class StatisticsSample {

    /**
     *  商品库里有每一个商品的价格，求产地为浙江省的商品中价格最低的商品价格是多少。
     *  等效的SQL是：SELECT min(column_price) FROM product where place_of_production="浙江省";
     */
    public void min(SyncClient client) {
        // 构建查询语句
        SearchRequest searchRequest = SearchRequest.newBuilder()
                .tableName("tableName")
                .indexName("indexName")
                .searchQuery(
                        SearchQuery.newBuilder()
                                .query(
                                        QueryBuilders.term("place_of_production","浙江省")
                                )
                                .limit(0)   // 如果只关心统计聚合的结果，返回匹配到的结果数量设置为0有助于提高响应速度。
                                .addAggregation(AggregationBuilders.min("min_agg_1", "column_price").missing(100))
                                .build())
                .build();
        //执行查询
        SearchResponse resp = client.search(searchRequest);
        //获取统计聚合的结果
        System.out.println(resp.getAggregationResults().getAsMinAggregationResult("min_agg_1").getValue());
    }

    /**
     * 商品库里有每一个商品的售出数量，求产地为浙江省的商品中，一共售出了多少件商品？如果某一件商品没有该值，默认售出了10件。
     * 等效的SQL是：SELECT sum(column_price) FROM product where place_of_production="浙江省";
     */
    public void sum(SyncClient client) {
        // 构建查询语句
        SearchRequest searchRequest = SearchRequest.newBuilder()
                .tableName("tableName")
                .indexName("indexName")
                .searchQuery(
                        SearchQuery.newBuilder()
                                .query(QueryBuilders.term("place_of_production", "浙江省"))
                                .limit(0)   // 如果只关心统计聚合的结果，返回匹配到的结果数量设置为0有助于提高响应速度。
                                .addAggregation(AggregationBuilders.sum("sum_agg_1", "column_number").missing(10))
                                .build())
                .build();
        //执行查询
        SearchResponse resp = client.search(searchRequest);
        //获取统计聚合的结果
        System.out.println(resp.getAggregationResults().getAsSumAggregationResult("sum_agg_1").getValue());
    }

    /**
     * 商品库里有每一个商品的售出数量，求产地为浙江省的商品中，平均价格是多少？
     *  等效的SQL是：SELECT avg(column_price) FROM product where place_of_production="浙江省";
     */
    public void avg(SyncClient client) {
        // 构建查询语句
        SearchRequest searchRequest = SearchRequest.newBuilder()
                .tableName("tableName")
                .indexName("indexName")
                .searchQuery(
                        SearchQuery.newBuilder()
                                .query(QueryBuilders.term("place_of_production", "浙江省"))
                                .limit(0)   // 如果只关心统计聚合的结果，返回匹配到的结果数量设置为0有助于提高响应速度。
                                .addAggregation(AggregationBuilders.avg("avg_agg_1", "column_number"))
                                .build())
                .build();
        //执行查询
        SearchResponse resp = client.search(searchRequest);
        //获取统计聚合的结果
        System.out.println(resp.getAggregationResults().getAsAvgAggregationResult("avg_agg_1").getValue());
    }

    /**
     * 商家库里有每一个商家的惩罚记录，求浙江省的商家中，有惩罚记录的一共有多少个商家？（假设没有惩罚记录的商家信息中不存在该字段）
     * 等效的SQL是：SELECT counter(column_history) FROM product where place_of_production="浙江省";
     */
    public void count(SyncClient client) {
        // 构建查询语句
        SearchRequest searchRequest = SearchRequest.newBuilder()
                .tableName("tableName")
                .indexName("indexName")
                .searchQuery(
                        SearchQuery.newBuilder()
                                .query(QueryBuilders.term("place", "浙江省"))
                                .limit(0)
                                .addAggregation(AggregationBuilders.count("count_agg_1", "column_history"))
                                .build())
                .build();
        //执行查询
        SearchResponse resp = client.search(searchRequest);
        //获取统计聚合的结果
        System.out.println(resp.getAggregationResults().getAsCountAggregationResult("count_agg_1").getValue());
    }

    /**
     * 求所有的商品，产地一共来自多少个省？
     * 等效的SQL是：SELECT counter(distinct column_place) FROM product;
     */
    public void distinctCount(SyncClient client) {
        // 构建查询语句
        SearchRequest searchRequest = SearchRequest.newBuilder()
                .tableName("tableName")
                .indexName("indexName")
                .searchQuery(
                        SearchQuery.newBuilder()
                                .query(QueryBuilders.matchAll())
                                .limit(0)
                                .addAggregation(AggregationBuilders.distinctCount("dis_count_agg_1", "column_place"))
                                .build())
                .build();
        //执行查询
        SearchResponse resp = client.search(searchRequest);
        //获取统计聚合的结果
        System.out.println(resp.getAggregationResults().getAsDistinctCountAggregationResult("dis_count_agg_1").getValue());
    }

    public void multipleAggregation(SyncClient client) {
        // 构建查询语句
        SearchRequest searchRequest = SearchRequest.newBuilder()
                .tableName("tableName")
                .indexName("indexName")
                .searchQuery(
                        SearchQuery.newBuilder()
                                .query(QueryBuilders.matchAll())
                                .limit(0)
                                .addAggregation(AggregationBuilders.min("name1", "long"))
                                .addAggregation(AggregationBuilders.sum("name2", "long"))
                                .addAggregation(AggregationBuilders.distinctCount("name3", "long"))
                                .build())
                .build();
        //执行查询
        SearchResponse resp = client.search(searchRequest);
        //获取第1个统计聚合的结果
        System.out.println(resp.getAggregationResults().getAsMinAggregationResult("name1").getValue());
        //获取第2个统计聚合的结果
        System.out.println(resp.getAggregationResults().getAsSumAggregationResult("name2").getValue());
        //获取第3个统计聚合的结果
        System.out.println(resp.getAggregationResults().getAsDistinctCountAggregationResult("name3").getValue());
    }



}
